SODA: Generating SQL for Business Users

The purpose of data warehouses is to enable business analysts to make better decisions. Over the years the technology has matured and data warehouses have become extremely successful. As a consequence, more and more data has been added to the data warehouses and their schemas have become increasingly complex. These systems still work great in order to generate pre-canned reports. However, with their current complexity, they tend to be a poor match for non tech-savvy business analysts who need answers to ad-hoc queries that were not anticipated. This paper describes the design, implementation, and experience of the SODA system (Search over DAta Warehouse). SODA bridges the gap between the business needs of analysts and the technical complexity of current data warehouses. SODA enables a Google-like search experience for data warehouses by taking keyword queries of business users and automatically generating executable SQL. The key idea is to use a graph pattern matching algorithm that uses the metadata model of the data warehouse. Our results with real data from a global player in the financial services industry show that SODA produces queries with high precision and recall, and makes it much easier for business users to interactively explore highly-complex data warehouses.Comment: VLDB201

The process of metadata modelling in industrial data warehouse environments

Modern application landscapes and especially huge enterprise applications, like data warehouses, used for decision support or other analyzing purposes get more and more complex. To manage, use and maintain these systems the need for metadata management has increased. In consequence of new tasks being identified by new groups of data warehouse users, the role of metadata management implies more than simply surf data schemas. It becomes necessary that metadata systems integrate different kinds of metadata and offer different views on the metadata as well. In this paper we discuss the process of identifying metadata model requirements, defining a new metadata model and finally implementing it in a metadata schema. The process is illustrated by a possible metadata model and schema, which were developed to meet the requirements of a complex data warehouse environment in Helsana Versicherungen AG, the largest Swiss insurance company. The paper describes the implementation of the metadata model based on the metadata standards Resource Description Framework (RDF) and RDF Schema (RDFS). The presented model and schema are just one possible solution and are not leading to a universal metadata model. The goal of this paper is to discuss the process of metadata modeling and to help metadata architects to develop their own metadata models and schemas

The Credit Suisse meta-data warehouse

This paper describes the meta-data warehouse ofCredit Suisse that is productive since 2009. Like most otherlarge organizations, Credit Suisse has a complex applicationlandscape and several data warehouses in order to meet theinformation needs of its users. The problem addressed by themeta-data warehouse is to increase the agility and flexibility ofthe organization with regards to changes such as the developmentof a new business process, a new business analytics report, or theimplementation of a new regulatory requirement. The meta-datawarehouse supports these changes by providing services to searchfor information items in the data warehouses and to extract thelineage of information items. One difficulty in the design of sucha meta-data warehouse is that there is no standard or well-knownmeta-data model that can be used to support such search services.Instead, the meta-data structures need to be flexible themselvesand evolve with the changing IT landscape. This paper describesthe current data structures and implementation of the CreditSuisse meta-data warehouse and shows how its services helpto increase the flexibility of the whole organization. A seriesof example meta-data structures, use cases, and screenshots aregiven in order to illustrate the concepts used and the lessonslearned based on feedback of real business and IT users withinCredit Suisse